﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Configuration;
using System.Drawing;

namespace Quality
{
    class sqlHelper
    {
        public static string loginname;
        static DataTable dtInfo = new DataTable();
        // 定义连接字符串     
        public static SqlConnection My_con;  //定义一个SqlConnection类型的公共变量My_con，用于判断数据库是否连接成功
        public static readonly string connstr =
          ConfigurationManager.ConnectionStrings["connection"].ConnectionString;

        public static int ExecuteNonQuery(string cmdText,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = cmdText;
                    cmd.CommandTimeout = 3000;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        public static void dgrd_Connection(string strSql, DataGridView dgrd, int start, int pagesize, string tableName)
        {

            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(strSql, getcon());
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet, start, pagesize, tableName);
                dtInfo = dataSet.Tables[tableName];
                dgrd.DataSource = dataSet.Tables[tableName];
                dgrd.AllowUserToAddRows = false;

            }
            catch { }
        }

        public static SqlConnection getcon()
        {


            My_con = new SqlConnection(connstr);   //用SqlConnection对象与指定的数据库相连接
            My_con.Open();  //打开数据库连接
            return My_con;  //返回SqlConnection对象的信息              


        }
        public static int Count(string strSql)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = strSql;
                        cmd.CommandTimeout = 3000;
                        int n = int.Parse(cmd.ExecuteScalar().ToString());
                        return n;

                    }
                    catch { return 0; }
                }
            }
        }
        public static object ExecuteScalar(string cmdText,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = cmdText;
                    cmd.CommandTimeout = 3000;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }

        public static DataTable ExecuteDataTable(string cmdText,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = cmdText;
                    cmd.CommandTimeout = 3000;
                    cmd.Parameters.AddRange(parameters);
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        adapter.Fill(dt);
                        return dt;
                    }
                }
            }
        }

        public static SqlDataReader ExecuteDataReader(string cmdText,
            params SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = cmdText;
                cmd.CommandTimeout = 3000;
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }

        
        //调用的时候parentId以0值开始 setTreeView(treeView1, 0);
        public static void setTreeView(TreeView tr1, int parentId)
        {
            string sql = "select * from organization where parentId=" + parentId;

            DataTable ds = sqlHelper.ExecuteDataTable(sql);
            if (ds.Rows.Count > 0)
            {
                int pId = -1;
                foreach (DataRow row in ds.Rows)
                {
                    TreeNode node = new TreeNode();
                    node.Text = row["nodeName"].ToString();
                    node.Tag = (int)row["id"];
                    pId = (int)row["parentId"];
                    if (pId == 0)
                    {
                        //添加根节点
                        tr1.Nodes.Add(node);
                    }
                    else
                    {
                        //添加根节点之外的其他节点
                        RefreshChildNode(tr1, node, pId);
                    }
                    //查找以node为父节点的子节点
                    setTreeView(tr1, (int)node.Tag);

                }
            }

        }


        //处理根节点的子节点
        public static void RefreshChildNode(TreeView tr1, TreeNode treeNode, int parentId)
        {
            foreach (TreeNode node in tr1.Nodes)
            {
                if ((int)node.Tag == parentId)
                {
                    node.Nodes.Add(treeNode);
                    return;
                }
                else if (node.Nodes.Count > 0)
                {
                    FindChildNode(node, treeNode, parentId);
                }
            }
        }
        //处理根节点的子节点的子节点
        public static void FindChildNode(TreeNode tNode, TreeNode treeNode, int parentId)
        {

            foreach (TreeNode node in tNode.Nodes)
            {
                if ((int)node.Tag == parentId)
                {
                    node.Nodes.Add(treeNode);
                    return;
                }
                else if (node.Nodes.Count > 0)
                {
                    FindChildNode(node, treeNode, parentId);

                }

            }

        }
         public static string getnum(string lx)
         {
         
        string date = DateTime.Now.ToString("yyyyMMdd");

       
         int count=  Count("select * from bianhao where danjulx='" + lx + "'");
        if (count <= 0)
        {
           
            ExecuteNonQuery("insert into bianhao (danjulx) values ('" + lx + "')");
           
        }
        DataTable dt = ExecuteDataTable("Select * from bianhao where bianhao like '%"+date+"%' and danjulx='" + lx + "'");
      
       
        if (dt.Rows.Count > 0)
        {
            DataRow row = dt.Rows[0];

            Int64 bianhao = Convert.ToInt64(row["bianhao"]) + 1;
            ExecuteNonQuery("update bianhao set bianhao='" + bianhao + "'where danjulx='" + lx + "'");
             return bianhao.ToString();
        }

        else
        {
            string bianhao = date + string.Format("{0:000}", 1);
            ExecuteNonQuery("update bianhao set bianhao='" + bianhao + "'where danjulx='" + lx + "'");
             return bianhao;
        }

       
         }
    
    }
    public delegate void CheckBoxClickedHandler(bool state);
    public class DataGridViewCheckBoxHeaderCellEventArgs : EventArgs
    {
        bool _bChecked;
        public DataGridViewCheckBoxHeaderCellEventArgs(bool bChecked)
        {
            _bChecked = bChecked;
        }
        public bool Checked
        {
            get { return _bChecked; }
        }
    }
    class DatagridViewCheckBoxHeaderCell : DataGridViewColumnHeaderCell
    {
        Point checkBoxLocation;
        Size checkBoxSize;
        bool _checked = false;
        Point _cellLocation = new Point();
        System.Windows.Forms.VisualStyles.CheckBoxState _cbState =
            System.Windows.Forms.VisualStyles.CheckBoxState.UncheckedNormal;
        public event CheckBoxClickedHandler OnCheckBoxClicked;

        public DatagridViewCheckBoxHeaderCell()
        {
        }

        protected override void Paint(System.Drawing.Graphics graphics,
            System.Drawing.Rectangle clipBounds,
            System.Drawing.Rectangle cellBounds,
            int rowIndex,
            DataGridViewElementStates dataGridViewElementState,
            object value,
            object formattedValue,
            string errorText,
            DataGridViewCellStyle cellStyle,
            DataGridViewAdvancedBorderStyle advancedBorderStyle,
            DataGridViewPaintParts paintParts)
        {
            base.Paint(graphics, clipBounds, cellBounds, rowIndex,
                dataGridViewElementState, value,
                formattedValue, errorText, cellStyle,
                advancedBorderStyle, paintParts);
            Point p = new Point();
            Size s = CheckBoxRenderer.GetGlyphSize(graphics,
            System.Windows.Forms.VisualStyles.CheckBoxState.UncheckedNormal);
            p.X = cellBounds.Location.X +
                (cellBounds.Width / 2) - (s.Width / 2);
            p.Y = cellBounds.Location.Y +
                (cellBounds.Height / 2) - (s.Height / 2);
            _cellLocation = cellBounds.Location;
            checkBoxLocation = p;
            checkBoxSize = s;
            if (_checked)
                _cbState = System.Windows.Forms.VisualStyles.
                    CheckBoxState.CheckedNormal;
            else
                _cbState = System.Windows.Forms.VisualStyles.
                    CheckBoxState.UncheckedNormal;
            CheckBoxRenderer.DrawCheckBox
            (graphics, checkBoxLocation, _cbState);
        }

        protected override void OnMouseClick(DataGridViewCellMouseEventArgs e)
        {
            Point p = new Point(e.X + _cellLocation.X, e.Y + _cellLocation.Y);
            if (p.X >= checkBoxLocation.X && p.X <=
                checkBoxLocation.X + checkBoxSize.Width
            && p.Y >= checkBoxLocation.Y && p.Y <=
                checkBoxLocation.Y + checkBoxSize.Height)
            {
                _checked = !_checked;
                if (OnCheckBoxClicked != null)
                {
                    OnCheckBoxClicked(_checked);
                    this.DataGridView.InvalidateCell(this);
                }

            }
            base.OnMouseClick(e);
        }
    }

}



